#include "remitool.h"
#include "ui_remitool.h"

ProxyDialog* Rt::proxy;
OnlineConnector* Rt::oc;
QStatusBar* Rt::statusbar;

RemiTool::RemiTool(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::RemiTool)
{
    ui->setupUi(this);

    db = QSqlDatabase::addDatabase(QLatin1String("QSQLITE"));
    db.setDatabaseName(QLatin1String("remitool.db"));
    if ( db.isOpen() || db.open() )
    {
        QSqlQuery q("PRAGMA encoding = UTF-8;", db);
        q.exec("CREATE TABLE IF NOT EXISTS `data` ( `data` TEXT PRIMARY KEY, `value` TEXT NOT NULL );");
        q.exec("CREATE TABLE IF NOT EXISTS stammdaten (objektnr TEXT PRIMARY KEY, vdznr TEXT DEFAULT NULL, gtin TEXT DEFAULT NULL, titel TEXT NOT NULL, preis TEXT DEFAULT NULL, multi INTEGER DEFAULT NULL);");
        q.exec("CREATE TABLE IF NOT EXISTS `archiv` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `datetime` TEXT );");
        q.exec("CREATE TABLE IF NOT EXISTS `remission` ( `id` INTEGER PRIMARY KEY AUTOINCREMENT, `titel` TEXT, `gtin` TEXT, `addon` TEXT, `menge` INTEGER, `preis` TEXT, `datetime` TEXT, `paket_id` INTEGER, `archiv_id` INTEGER );");
        q.exec("CREATE TABLE IF NOT EXISTS online_sortiment ( objektnr TEXT NOT NULL PRIMARY KEY, vdznr TEXT NOT NULL, titel TEXT NOT NULL, og_id TEXT NOT NULL, og_bezeichnung TEXT NOT NULL );");
        q.exec("CREATE TABLE IF NOT EXISTS online_pakete ( paketnr TEXT NOT NULL, lfdnr TEXT NOT NULL, verarbeitung TEXT NOT NULL );");
        q.exec("CREATE TABLE IF NOT EXISTS online_remis ( objektnr TEXT NOT NULL, anr TEXT NOT NULL, menge INTEGER NOT NULL, bezeichnung TEXT NOT NULL, paketnr TEXT NOT NULL REFERENCES online_pakete ( paketnr ) ON DELETE CASCADE ON UPDATE CASCADE );");
        q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");
        if ( !q.first() && ( db.isOpen() || db.open() ) )
        {
            QSqlQuery("INSERT INTO `data` (`data`, `value`) VALUES ('version', '1.4.0');", db);
        }
        else
        {
            /** Update von Version 1.1 auf Version 1.11 **/
            if ( q.value(0).toString() == "1.1" && ( db.isOpen() || db.open() ) )
            {
                QSqlQuery("BEGIN TRANSACTION;", db);
                QSqlQuery("ALTER TABLE `stammdaten` ADD 'issue' INTEGER;", db);
                QSqlQuery("UPDATE `stammdaten` SET `issue` = '0';");
                QSqlQuery("UPDATE `data` SET `value` = '1.11' WHERE `data` = 'version';", db);
                QSqlQuery("COMMIT;", db);
                q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");

            }

            /** Update von Version 1.11 auf Version 1.2 **/
            if ( q.first() && q.value(0).toString() == "1.11" && ( db.isOpen() || db.open() ) )
            {
                /** Feld 'Untertitel' entfernen **/
                QSqlQuery("BEGIN TRANSACTION;", db);
                QSqlQuery("CREATE TEMPORARY TABLE TEMP_TABLE(object_id TEXT PRIMARY KEY, titel TEXT, preis TEXT, issue INTEGER);", db);
                QSqlQuery("INSERT INTO TEMP_TABLE SELECT object_id, titel, preis, issue FROM stammdaten;", db);
                QSqlQuery("DROP TABLE stammdaten;", db);
                QSqlQuery("CREATE TABLE stammdaten (object_id TEXT PRIMARY KEY, titel TEXT, preis TEXT, issue INTEGER);", db);
                QSqlQuery("INSERT INTO stammdaten(object_id, titel, preis, issue) SELECT object_id, titel, preis, issue FROM TEMP_TABLE;", db);
                QSqlQuery("DROP TABLE TEMP_TABLE;", db);
                QSqlQuery("CREATE TEMPORARY TABLE TEMP_TABLE(id INTEGER PRIMARY KEY, titel TEXT, gtin TEXT, addon TEXT, menge INTEGER, preis TEXT, datetime TEXT, archiv_id INTEGER, paket_id INTEGER);", db);
                QSqlQuery("INSERT INTO TEMP_TABLE SELECT id, titel, gtin, addon, menge, preis, datetime, archiv_id, paket_id FROM remission;", db);
                QSqlQuery("DROP TABLE remission;", db);
                QSqlQuery("CREATE TABLE remission (id INTEGER PRIMARY KEY, titel TEXT, gtin TEXT, addon TEXT, menge INTEGER, preis TEXT, datetime TEXT, archiv_id INTEGER, paket_id INTEGER);", db);
                QSqlQuery("INSERT INTO remission(id, titel, gtin, addon, menge, preis, datetime, archiv_id, paket_id) SELECT id, titel, gtin, addon, menge, preis, datetime, archiv_id, paket_id FROM TEMP_TABLE;", db);
                QSqlQuery("DROP TABLE TEMP_TABLE;", db);
                QSqlQuery("UPDATE `data` SET `value` = '1.2' WHERE `data` = 'version';", db);
                QSqlQuery("COMMIT;", db);
                q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");
            }

            /** Update von Version 1.2 auf Version 1.3 **/
            if ( q.first() && q.value(0).toString() == "1.2" && ( db.isOpen() || db.open() ) )
            {
                QSqlQuery("BEGIN TRANSACTION;", db);
                QSqlQuery("UPDATE `data` SET `value` = '1.3' WHERE `data` = 'version';", db);
                QSqlQuery("COMMIT;", db);
                q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");
            }

            /** Update von Version 1.3 auf Version 1.31 **/
            if ( q.first() && q.value(0).toString() == "1.3" && ( db.isOpen() || db.open() ) )
            {
                QSqlQuery("BEGIN TRANSACTION;", db);
                QSqlQuery("UPDATE `data` SET `value` = '1.31' WHERE `data` = 'version';", db);
                QSqlQuery("COMMIT;", db);
                q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");
            }

            /** Update von Version 1.31 auf Version 1.32 **/
            if ( q.first() && q.value(0).toString() == "1.31" && ( db.isOpen() || db.open() ) )
            {
                QSqlQuery("BEGIN TRANSACTION;", db);
                QSqlQuery("UPDATE `data` SET `value` = '1.32' WHERE `data` = 'version';", db);
                QSqlQuery("COMMIT;", db);
                q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");
            }

            /** Update von Version 1.32 auf Version 1.4.0 **/
            if ( q.first() && q.value(0).toString() == "1.32" && ( db.isOpen() || db.open() ) )
            {
                q.exec("BEGIN TRANSACTION;");
                q.exec("CREATE TABLE stammdaten_backup (object_id TEXT PRIMARY KEY, titel TEXT, preis TEXT, issue INTEGER);");
                q.exec("INSERT INTO stammdaten_backup SELECT object_id, titel, preis, issue FROM stammdaten;");
                q.exec("DROP TABLE stammdaten;");
                q.exec("CREATE TABLE stammdaten (objektnr TEXT PRIMARY KEY, vdznr TEXT DEFAULT NULL, gtin TEXT DEFAULT NULL, titel TEXT NOT NULL, preis TEXT DEFAULT NULL, multi INTEGER DEFAULT NULL);");

                q.exec("SELECT object_id, titel, preis FROM stammdaten_backup;");
                while (q.next())
                {
                    QSqlQuery query(db);
                    query.prepare("INSERT INTO stammdaten ( objektnr, vdznr, gtin, titel, preis ) VALUES ( :objektnr, :vdznr, :gtin, :titel, :preis );");
                    query.bindValue(":objektnr", "rt" + q.value(0).toString());
                    if ( da.isGTIN(q.value(0).toString()) )
                        query.bindValue(":gtin", q.value(0).toString());
                    else
                        query.bindValue(":vdznr", q.value(0).toInt());
                    query.bindValue(":titel", q.value(1));
                    query.bindValue(":preis", q.value(2));
                    query.exec();
                }
                q.exec("DROP TABLE stammdaten_backup;");

                q.exec("SELECT id, datetime FROM archiv;");
                while (q.next())
                {
                    QSqlQuery query(db);
                    query.prepare("UPDATE archiv SET datetime = :datetime WHERE id = :id;");
                    query.bindValue(":id", q.value(0).toInt());
                    query.bindValue(":datetime", q.value(1).toDateTime());
                    query.exec();
                }

                q.exec("UPDATE `data` SET `value` = '1.4.0' WHERE `data` = 'version';");
                q.exec("COMMIT;");
                q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");
            }

            /** Update von Version 1.4.0 auf Version 1.5.0 **/
//            if ( q.first() && q.value(0).toString() == "1.4.0" && ( db.isOpen() || db.open() ) )
//            {
//                q.exec("BEGIN TRANSACTION;");
//                q.exec("UPDATE `data` SET `value` = '1.5.0' WHERE `data` = 'version';");
//                q.exec("COMMIT;");
//                q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");
//            }
        }

        q.exec("SELECT `value` FROM `data` WHERE `data` = 'version';");
        programm_version = q.first()?q.value(0).toString():"n/a";
        q.finish();
    }

    loadSettings();

    /** Proxy und Online-Verbindung einrichten **/
    Rt::proxy = new ProxyDialog(this);
    Rt::oc = new OnlineConnector(statusBar(), this);
    connect(Rt::proxy, SIGNAL(proxyChanged()), Rt::oc, SLOT(reconnect()));

    /** Tabs laden **/
    remi = new Remi();
    archiv = new Archiv();
    stammdaten = new Stammdaten();
    onlineremi = new OnlineRemi();
    onlinesortiment = new OnlineSortiment();

    archiv->reloadArchive();

    ui->tabWidget->addTab(remi, tr("aktuelle Remission"));
    ui->tabWidget->addTab(archiv, tr("Archiv"));
    ui->tabWidget->addTab(stammdaten, tr("Stammdaten"));
    ui->tabWidget->addTab(onlineremi, tr("Online Remission"));
    ui->tabWidget->addTab(onlinesortiment, tr("Online Sortiment"));

    Rt::statusbar = this->statusBar();

    connect(remi, SIGNAL(archiviert()), this, SLOT(goToArchiv()));
    connect(ui->action_ber_Qt, SIGNAL(triggered()), qApp, SLOT(aboutQt()));
    connect(ui->actionBeenden, SIGNAL(triggered()), qApp, SLOT(quit()));
    connect(qApp, SIGNAL(aboutToQuit()), this, SLOT(cleanDatabase()));
    connect(qApp, SIGNAL(aboutToQuit()), this, SLOT(saveSettings()));
}

RemiTool::~RemiTool()
{
    db.close();
    delete ui;
}

void RemiTool::saveSettings()
{
    QSettings settings;
    settings.setValue("mainwindow/maximized", isMaximized());
    if ( isMaximized() )
        showNormal();
    settings.setValue("mainwindow/size", size());
    settings.setValue("mainwindow/pos", pos());
}

void RemiTool::loadSettings()
{
    QSettings settings("FHsoft", "RemiTool");
    resize(settings.value("mainwindow/size", QSize(720, 480)).toSize());
    move(settings.value("mainwindow/pos", QPoint()).toPoint());
    if ( settings.value("mainwindow/maximized", false).toBool() )
        showMaximized();
}

void RemiTool::goToArchiv()
{
    archiv->reloadArchive();
    archiv->markFirstRemi();
    ui->tabWidget->setCurrentWidget(archiv);
}

void RemiTool::on_action_ber_RemiTool_triggered()
{
    QMessageBox *box = new QMessageBox(this);
    box->setWindowTitle(tr("Über RemiTool"));
    box->setText(tr("Version: ") + programm_version);
    box->setIconPixmap(QPixmap(":/images/remitool.ico"));
    box->setStandardButtons(QMessageBox::Ok);
    box->exec();
}

void RemiTool::cleanDatabase()
{
    if ( db.isOpen() || db.open() )
    {
        QSqlQuery("VACUUM;", db);
    }
}

void RemiTool::on_actionOnline_Login_Daten_triggered()
{
    OnlineLoginForm *f = new OnlineLoginForm(this);
    f->exec();
}

void RemiTool::on_actionProxy_konfigurieren_triggered()
{
    Rt::proxy->exec();
}
